{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Musicians - Easy\n",
    "\n",
    "## Tables used in the musicians database\n",
    "\n",
    "band(**band_no**, band_name, band_home, band_type, b_date, band_contact)\n",
    "\n",
    "composer(**comp_no**, comp_is, comp_type)\n",
    "\n",
    "composition(**c_no**, comp_date, c_title, c_in)\n",
    "\n",
    "concert(**concert_no**, concert_venue, concert_in, con_date, concert_orgniser)\n",
    "\n",
    "has_composed(**_cmpr_no_**, **_cmpn_no_**)\n",
    "\n",
    "musician(**m_no**, m_name, born, died, born_in, living_in)\n",
    "\n",
    "performance(**_pfrmnc_no_**, gave, performed, conducted_by, performed_in)\n",
    "\n",
    "performer(**perf_no**, perf_is, instrument, perf_type)\n",
    "\n",
    "place(**place_no**, place_town, place_country)\n",
    "\n",
    "plays_in(**_player_**, **band_id**)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "library(tidyverse)\n",
    "library(DBI)\n",
    "library(getPass)\n",
    "drv <- switch(Sys.info()['sysname'],\n",
    "             Windows=\"PostgreSQL Unicode(x64)\",\n",
    "             Darwin=\"/usr/local/lib/psqlodbcw.so\",\n",
    "             Linux=\"PostgreSQL\")\n",
    "con <- dbConnect(\n",
    "  odbc::odbc(),\n",
    "  driver = drv,\n",
    "  Server = \"localhost\",\n",
    "  Database = \"sqlzoo\",\n",
    "  UID = \"postgres\",\n",
    "  PWD = getPass(\"Password?\"),\n",
    "  Port = 5432\n",
    ")\n",
    "options(repr.matrix.max.rows=20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "**Give the organiser's name of the concert in the Assembly Rooms after the first of Feb, 1997.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "**Find all the performers who played guitar or violin and were born in England.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "**List the names of musicians who have conducted concerts in USA together with the towns and dates of these concerts.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "**How many concerts have featured at least one composition by Andy Jones? List concert date, venue and the composition's title.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "**List the different instruments played by the musicians and avg number of musicians who play the instrument.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
